﻿using System;
using System.Data;
using System.Configuration;

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using DAL.DFactory;
using DAL.DHelpers;
using System.Collections.Generic;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace DAL.DCollector
{
    public class DBCollector
    {

        private DatabaseProvider name ;
        public static DBCollector Instance = new DBCollector(DatabaseProvider.MySQL);
        private MappingTable table ;
        public DBCollector(DatabaseProvider name)
        {
            this.name = name;
           
        }
        public DBCollector(DatabaseProvider name, MappingTable table)
        {
            this.name = name;
            this.table = table;

        }
        #region Tập hợp thao tác SELECT
        public DataTable SelectSQL(String sql)

        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))

                return DbManager.ExecuteDataTable(sql);
        }

        public DataTable SelectTable(String table)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT * FROM "+ table);
        }
    
        public DataTable SelectCols(String table,string Cols)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT " +Cols+ " FROM " + table);
        }
     
        public DataTable SelectColsByWhere(String table,string Cols, string Condition)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT " +Cols +" FROM " + table + "WHERE " + Condition);
        }
    
        public DataTable SelectAllByWhere(String table,string Condition)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT * FROM " + table + "WHERE "+Condition);
        }

        public DataTable SelectAllByColunmnInArrays(string table ,string ColumnName, string []ColArrays)
            {
                String strQuery = String.Empty;
                strQuery += "SELECT * FROM " + table; 
                strQuery+=" WHERE " + ColumnName + " IN ( " ;
                strQuery+=ParamsCreator.instance.GetSeparateString(ColArrays) ;
                strQuery+= ")";
                return DatabaseManager.SqlServerInstance.ExecuteDataTable(strQuery); 
            }
        public DataTable SelectAllByColunmnInArrays(string table, string ColumnName, string ColArrays)
        {
            String strQuery = String.Empty;
            strQuery += "SELECT * FROM " + table;
            strQuery += " WHERE " + ColumnName + " IN ( ";
            strQuery += ColArrays;
            strQuery += ")";
            return DatabaseManager.SqlServerInstance.ExecuteDataTable(strQuery);
        }
        public DataTable SelectAllByWhere(string table, string parNames, string parValues, string typeValues)
        {

            String sqlString = String.Empty;
            sqlString += " SELECT * FROM " + table;

            sqlString += GetWhereCondition(parNames.Split(','));
            MySqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues, typeValues);
            return DatabaseManager.SqlServerInstance.ExecuteDataTable(sqlString, myParams);



        }

        public DataTable SelectAllByWhere(string table, string[] parNames, object[] parValues)
        {

            String sqlString = String.Empty;
            sqlString += " SELECT * FROM " + table;

            sqlString += GetWhereCondition(parNames);
            MySqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues);
           return  DatabaseManager.SqlServerInstance.ExecuteDataTable(sqlString, myParams);



        }
        // lay ve so ban ghi cua mot bang of Duyen pro
        public int countRecordofTable(string table)
        {
            
            // dung 
            int sobanghi = 0;
            string query =" Select count(*) as count from "+table;
            DataTable nametable;
            nametable=SelectSQL(query);
            sobanghi = Convert.ToInt32(getValue(0,0,nametable));
            
            return sobanghi;
        }
        public string getValue(int col, int row,DataTable dt)
        {
            return dt.Rows[row].ItemArray[col].ToString();
        }

        #endregion

        #region Tập hợp thao tác DELETE
        public void Delete(String table, String Condition)
        {

            DatabaseManager.SqlServerInstance.ExecuteNonQuery("DELETE FROM " + table + " WHERE " + Condition);
        }

        public void DeleteByWhere(string table, string parNames, string parValues, string typeValues)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;

            sqlString += GetWhereCondition(parNames.Split(','));
            MySqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues, typeValues);
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);

        }
        public void DeleteByColumnInArrays(string table, string ColumnName, string arraysValue)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;
            sqlString += " WHERE " + ColumnName + " IN (" + arraysValue + ")";
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString);

        }
        public void DeleteByColumnInArrays(string table, string ColumnName, string[] Values)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;
            sqlString += " WHERE " + ColumnName + " IN (" + ParamsCreator.instance.GetSeparateString(Values) + ")";
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString);

        }

        public void DeleteByWhere(string table, string[] parNames, object[] parValues)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;

            sqlString += GetWhereCondition(parNames);
            MySqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues);
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);



        }
        #endregion

        #region Tập hợp thao tác INSERT
     
        public void Insert ( string table , Column [] Values )
         {
            
             String sqlString = String.Empty;
             sqlString += " INSERT INTO " + table + "(";
             sqlString += GetValuesInsert(Values);
             sqlString += ")";

             MySqlParameter[] myParams = ParamsCreator.instance.GetParams(Values);
             DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString,myParams);

         }
        #endregion

        #region Tập hợp thao tác UPDATE
        public void Update(String table, string[] ColsUpdate, object[] parUpdate, string[] ColsWhere, object[] parWhere)
        {
            this.table = new MappingTable(table,true);
            String sqlString = String.Empty;
            sqlString += " UPDATE  " + table + "  SET ";
            sqlString += GetValuesUpdate(ColsUpdate);
            sqlString += GetWhereCondition(ColsWhere);
            MySqlParameter[] myParams1 = ParamsCreator.instance.GetParams( ColsUpdate, parUpdate);
            MySqlParameter[] myParams2 = ParamsCreator.instance.GetParams(ColsWhere, parWhere);
            MySqlParameter[] myParams = ParamsCreator.instance.GetParams(myParams1, myParams2);
            
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);
        }
        public void Update(string table, Column[] Values, string[] ColsWhere, object[] parWhere)
        {

            String sqlString = String.Empty;
            sqlString += " UPDATE  " + table + " SET ";
            sqlString += GetValuesUpdate(Values);
            sqlString += "";
            sqlString += GetWhereCondition(ColsWhere);
            MySqlParameter[] myParams1 = ParamsCreator.instance.GetParams(Values);
            MySqlParameter[] myParams2 = ParamsCreator.instance.GetParams(ColsWhere, parWhere);
            MySqlParameter[] myParams = ParamsCreator.instance.GetParams(myParams1, myParams2);
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);
        }
    #endregion
   
        #region Các hàm TIỆN ÍCH
        private String GetWhereCondition(string[] parNames)
        {
            String strSql = String.Empty;
            if (parNames.Length > 0)
            {
                strSql += " WHERE ";
                foreach (string p in parNames)
                {
                    strSql += p + " = @" + p + " AND ";

                }
                strSql += " 1 = 1 ";
            }
            return strSql;
        }
        private String GetValuesInsert(String table,List<string> values)
        {
            this.table = new MappingTable(table,true);
            String strSql = String.Empty;
            //string str1 = String.Empty;
            string str2 = String.Empty;
            if (this.table.Columns.Count > 0)
            {
                int n = this.table.Columns.Count;
              //  str1 += " ( ";
              //  str2 += " ( ";
                int i = 0;
                foreach (Column p in this.table.Columns.Values)
                {

                    if (p.IsPrimaryKey == false)
                    {
                        if (p.DataType == DataType.DataTypeSql.Nvarchar)
                        {
                            str2 += "N'" + values[i] + "',";
                        }
                        if (p.DataType == DataType.DataTypeSql.Int)
                        {
                            str2 +=   values[i] + ",";
                        }
                        if (p.DataType == DataType.DataTypeSql.Varchar)
                        {
                            str2 += "'" + values[i] + "',";
                        }
                        if (p.DataType == DataType.DataTypeSql.Datetime)
                        {
                            str2 += "'" + values[i] + "',";
                        }
                    }
                    i++;

                }
                //str1 += " )";
               // str2 += " )";
                strSql = str2.PadLeft(str2.Length - 1); 
                return strSql;

            }
            return strSql;
        }
        private String GetColumnsInsert(String table)
        {
            string strSql = "";
         
            this.table = new MappingTable(table, true);

            if (this.table.Columns.Count > 0)
            {
               
                foreach (Column p in this.table.Columns.Values)
                {

                    if (p.IsPrimaryKey == false)
                    {
                        strSql += p.ColumnName+",";
                    }
                   

                }
            }

            return strSql.PadLeft(strSql.Length - 1); 
        }
        private String GetValuesInsert(Column[] Values)
        {
            int n = Values.Length;
            String str = String.Empty;
            int i = 0;
            for (; i < n - 1; i++)
            {
                str += Values[i].ColumnName + ",";
            }
            str += Values[i].ColumnName;
            str += ")Values(";
            i = 0;
            for (; i < n - 1; i++)
            {
                str += "@"+Values[i].ColumnName + ",";
            }
            str += "@"+Values[i].ColumnName;
            return str;
        }
        private String GetValuesUpdate(String[] Values)
        {
            int n = Values.Length;
            String str = String.Empty;
            int i = 0;
            for (; i < n - 1; i++)
            {
                str += Values[i] + "= @" + Values[i] + ",";
            }
            str += Values[i] + "= @" + Values[i];
            return str;


        }
        private String GetValuesUpdate(Column[] Values)
        {
            int n = Values.Length;
            String str = String.Empty;
            int i = 0;
            for (; i < n - 1; i++)
            {
                str += Values[i].ColumnName + "= @" + Values[i].ColumnName + ",";
            }
            str += Values[i].ColumnName + "= @" + Values[i].ColumnName;
            return str;


        }
        #endregion


        #region STORED PROCEDURE
        public DataTable CallSQL1Param(string procedureName, string param1, bool b)
        {
            string[] parName = new string[1];
            parName[0] = "param1";

            string[] parvalue = new string[1];
            parvalue[0] = param1;

            MySqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.MySqlInstance.CallSQLProcedure(procedureName, p, b);
        }
        public DataTable CallSQL1Param( string param1, bool b)
        {
            return DatabaseManager.MySqlInstance.ExecuteSQL( param1, b);
        }
        public DataTable StoredSelectTable(string tableName)
        {
            string sql = "select * from " + tableName;

            return CallSQL1Param( sql, true);

        }
        public DataTable StoredSelectTable(string tableName, string OrderbyColumn, string Sort)
        {
            string sql = "select * from " + tableName + " Order by " + OrderbyColumn + " " + Sort;

            return CallSQL1Param( sql, true);

        }
        public DataTable StoredSelectTable(string tableName,int soluong, string OrderbyColumn, string Sort)
        {
            string sql = "select * from " + tableName + " Order by " + OrderbyColumn + " " + Sort + " LIMIT " + soluong ;

            return CallSQL1Param(sql, true);

        }
        public DataTable StoredSelectTableWhereID(string tableName, string column, string id)
        {

            string sql = "select * from " + tableName + " where " + column + " = " + id;

            return CallSQL1Param( sql, true);

        }
        public DataTable StoredSelectTableWhereID(string tableName, string id)
        {
            string column = tableName.Replace("tbl_", "") + "_ID";
            string sql = "select * from " + tableName + " where " + column + " = " + id;

            return CallSQL1Param(sql, true);

        }

        public void StoredInsert(string table, string value)
        {
            string sql = " insert into " + table + " (" + DAL.DHelpers.MappingTable.getColumns(table) + ") values (" + value + ")";
            CallSQL1Param( sql, false);
        }

        public void StoredDelete(string table, string id)
        {
            string column = table.Replace("tbl_", "") + "_ID";
            string sql = " delete from " + table + " where " + column + " = " + id;
            CallSQL1Param( sql, false);
        }
        public void StoredDelete(string table, string column, string id)
        {

            string sql = " delete from " + table + " where " + column + " = " + id;
            CallSQL1Param( sql, false);
        }
        public void StoredUpdate(string table, Column[] Values, string column, string id)
        {

            string sql = " Update " + table + " set " + getParamUpdate(Values) + " where " + column + " = " + id;
            CallSQL1Param( sql, false);
        }
        private string getParamUpdate(Column[] Values)
        {
            string s = "";
            for (int i = 0; i < Values.Length; i++)
            {
                if (i < Values.Length - 1)
                {
                    if (Values[i].DataType == DataType.DataTypeSql.Int)
                        s += Values[i].ColumnName + " = " + Values[i].Value + ",";
                    if (Values[i].DataType == DataType.DataTypeSql.Varchar)
                        s += Values[i].ColumnName + " = '" + Values[i].Value + "',";
                    if (Values[i].DataType == DataType.DataTypeSql.Nvarchar)
                        s += Values[i].ColumnName + " = N'" + Values[i].Value + "',";
                }
                else
                {
                    if (Values[i].DataType == DataType.DataTypeSql.Int)
                        s += Values[i].ColumnName + " = " + Values[i].Value;
                    if (Values[i].DataType == DataType.DataTypeSql.Varchar)
                        s += Values[i].ColumnName + " = '" + Values[i].Value + "'";
                    if (Values[i].DataType == DataType.DataTypeSql.Nvarchar)
                        s += Values[i].ColumnName + " = N'" + Values[i].Value + "'";

                }

            }
            return s;
        }
        #endregion
    }
   
}
